package com.itheima.health.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itheima.health.vo.ReportSetmemlVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

public interface ReportMapper extends BaseMapper {

    @Select("SELECT count(tm.ID) AS memberCounts," +
            "concat(YEAR(rp.fdate),'-',rp.fmonth) AS " +
            "yearAndMonth FROM rpt_date rp,t_member tm WHERE " +
            "rp.fdate = tm.REGTIME AND rp.fdate BETWEEN #{startDate} " +
            "AND #{endDate} GROUP BY rp.fmonth")
    List<Map>findLastyearMemberCountsByMonth(@Param("startDate") String startDate, @Param("endDate")String endDate);

    @Select("Select case s.sex when '1' THEN '男' ELSE '女' END as name,count(s.sex) as value from t_member s GROUP BY sex")
    List<Map>getSexReport();

    @Select(" SELECT name,COUNT(*) AS value FROM (\n" +
            "SELECT age,\n" +
            "CASE\n" +
            "WHEN age BETWEEN 0 AND 18 THEN '0-18'\n" +
            "WHEN age BETWEEN 18 AND 30 THEN '18-30'\n" +
            "WHEN age BETWEEN 30 AND 45 THEN '30-45'\n" +
            "WHEN age > 45 THEN '45以上'\n" +
            "END AS name\n" +
            "FROM t_member\n" +
            ")AS USER GROUP BY name")
    List<Map> getAgeReport();

//    @Select("SELECT s. NAME as name, count(tor.setmeal_id) as value " +
//            "FROM t_order tor INNER JOIN t_setmeal s " +
//            "ON tor.setmeal_id = s.id " +
//            "GROUP BY tor.setmeal_id")
//    List<Map> getSetmealProportion();


    //今日新增会员数
    @Select("select count( 1 ) "  +
            " from t_member tm " +
            " where to_days(tm.REGTIME) = to_days(now());")
    int todayNewMember();

    //总会员数
    @Select("select count(1) from t_member")
    int totalMember();


    //本周新增会员
    @Select("select count(1) " +
            "from t_member tm " +
            "where tm.REGTIME = week(now());")
    int thisWeekNewMember();


    //本月新增会员
    @Select("SELECT count(1) " +
            "FROM t_member tm " +
            "where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(tm.REGTIME)")
    int thisMonthNewMember();


    //今日预约数
    @Select("select count(1) " +
            "from t_order tor " +
            "where to_days(tor.ORDERDATE) = to_days(now());")
    int todayOrderNumber();

    //今日到诊数
    @Select("select count(1) " +
            "from t_order tor  " +
            "where to_days(tor.ORDERDATE) = to_days(now()) and ORDERSTATUS ='到诊'")
    int todayVisitsNumber();

    //本周到诊断
    @Select("select count(1) " +
            "FROM  t_order tor " +
            "WHERE YEARWEEK(date_format(tor.ORDERDATE,'%Y-%m-%d')) = YEARWEEK(now()) and ORDERSTATUS ='到诊'")
    int thisWeekVisitsNumber();

    //本周预约数
    @Select("SELECT count(1) " +
            "FROM  t_order tor " +
            "WHERE YEARWEEK(date_format(tor.ORDERDATE,'%Y-%m-%d')) = YEARWEEK(now());")
    int thisWeekOrderNumber();

    //本月预约数
    @Select("select count(1)" +
            "from t_order tor " +
            "where  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(tor.ORDERDATE)")
    int thisMonthOrderNumber();


    //本月到诊数
    @Select("select count(1) " +
            "from t_order tor " +
            "where  DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(tor.ORDERDATE) and ORDERSTATUS ='到诊'")
    int thisMonthVisitsNumber();

    //热门套餐
    @Select("select ts.name ,count(ts.id) as setmealCount ,ts.REMARK as remark" +
            " from t_setmeal ts, t_order tor " +
            " where ts.id = tor.SETMEAL_ID " +
            " group by ts.id" +
            " LIMIT 0,3")
    List<ReportSetmemlVo> findHotSetmeal();



    //总预约数
    @Select("select count(1) " +
            "from  t_order")
    int findAllOrder();

}
